﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF ( OBJECT_ID(N'[dbo].[sproc_Mail_SendMail_V3]', 'P') IS NOT NULL )
BEGIN
    DROP PROCEDURE [dbo].[sproc_Mail_SendMail_V3]
END
GO

CREATE PROCEDURE [dbo].[sproc_Mail_SendMail_V3]
    @Sender         nvarchar(50),
    @TOList         nvarchar(1024),
    @CCList         nvarchar(1024) = NULL,
    @BCCList        nvarchar(1024) = NULL,
    @RecipientXml   xml,    -- a list of users who should receive the mail, including CC/BCC users.
    @Subject        nvarchar(256),
    @Body           nvarchar(max),
    @ClassID        int,
    @Importance     int,
    @SendSMS		bit,
    @MailBodyID     int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @smsFlag int;
SET @smsFlag = 0;
IF ( @SendSMS = 1 )
	SET @smsFlag = 1;

SET @MailBodyID = -1;
IF ( @@TRANCOUNT = 0 )
BEGIN
    RAISERROR(N'This sproc must be executed within an active transaction.', 16, 1);
    RETURN -1;
END

/*
 * XML format
 * <recipients>
 *  <r name="" />
 * </recipients>
 */
DECLARE @tmp TABLE
(
    UserName nvarchar(50) NOT NULL
);
INSERT INTO @tmp(UserName)
    SELECT DISTINCT X.r.value(N'@name', N'nvarchar(50)')
    FROM @RecipientXml.nodes(N'/recipients/r') X(r);

-- Create the mail
INSERT INTO dbo.UDS_MailBody (Creator, ToList, CCList, BCCList, Subject, Body, Importance, ExternalMail, ClassID)
    VALUES(@Sender, @TOList, @CCList, @BCCList, @Subject, @Body, @Importance, 0, @ClassID);
SET @MailBodyID = @@IDENTITY;

-- Save the mail in the OutBox of the sender
INSERT INTO dbo.UDS_Mail (UserName, FolderType, MailBodyId, ReadFlag)
    VALUES (@Sender, /* OutBox */2, @MailBodyID, /* mark as read */ 1);

-- Send the mail to all recipients
INSERT INTO dbo.UDS_Mail (UserName, FolderType, MailBodyId, ReadFlag, SMSFlag)
    SELECT UserName, /* InBox */1, @MailBodyID, /* a new mail */ 0, @smsFlag
    FROM @tmp;

END
GO
